package br.com.educa.componentes.datatables;

import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

public class DataTablesSql {
	
	
	@SuppressWarnings("unused")
	public static String doBServerSideSql(Map<String,String[]> parameters, String table, String addicionalWhere){
		
		HashMap<Integer,Boolean> bRegexs = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSearchables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSortables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Integer> iSortCols = new HashMap<Integer,Integer>();
		HashMap<Integer,String> mDataProps = new HashMap<Integer,String>();
		HashMap<Integer,String> sSearchs = new HashMap<Integer,String>();
		HashMap<Integer,String> sSortDirs = new HashMap<Integer,String>();
		HashMap<Integer,String> columnNames = new HashMap<Integer,String>();
		
		for (Entry<String,String[]> entry : parameters.entrySet()) {
			if(entry.getKey().startsWith("bRegex_")){
				bRegexs.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSearchable_")){
				bSearchables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSortable_")){
				bSortables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("iSortCol_")){
				iSortCols.put(Integer.parseInt(entry.getKey().split("_")[1]), Integer.parseInt(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("mDataProp_")){
				mDataProps.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSearch_")){
				sSearchs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSortDir_")){
				sSortDirs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("columName_")){
				columnNames.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}
		}
		
		String[] aColumns = new String[columnNames.values().size()];
		int contador=0; 
		for (String columnName : columnNames.values()) {
			aColumns[contador]=columnName;
			contador++;
		}
		
		 
		
		if(iSortCols.size()<bSearchables.size()){
			for (Entry<Integer,Boolean> entry : bSearchables.entrySet()) {
				if(!iSortCols.containsKey(entry.getKey())){
					iSortCols.put(entry.getKey(), null);
				}
			}
		}
		
		for (Entry<Integer,String> entry : sSearchs.entrySet()) {
			if(entry.getValue().isEmpty()){
				entry.setValue(null);
			}
		}
			
		
		Boolean bRegex = Boolean.parseBoolean(parameters.get("bRegex")[0]);
		Integer iColumns = Integer.parseInt(parameters.get("iColumns")[0]);
		Integer iDisplayLength = Integer.parseInt(parameters.get("iDisplayLength")[0]);
		Integer iDisplayStart = Integer.parseInt(parameters.get("iDisplayStart")[0]);
		Integer iSortingCols = Integer.parseInt(parameters.get("iSortingCols")[0]);
		String sColumns = parameters.get("sColumns")[0];
		Integer sEcho = Integer.parseInt(parameters.get("sEcho")[0]);		
		String sSearch = parameters.get("sSearch")[0];
		sSearch = sSearch.replaceAll("'", "").replaceAll("\"", "").replaceAll("\'", "");
		
		
		/* Paging */
		String sLimit = "";

		if ( iDisplayStart!=null && iDisplayLength !=-1){
			sLimit = " limit " + iDisplayLength + " offset " + iDisplayStart;
		}
		
		
		/* Ordering */
//		String sOrder = " ORDER BY  ";
//		int cont=0;
//		for (int i =0; i<aColumns.length; i++) {
//			if ( iSortCols.get(i)!=null){
//				if(bSortables.get( iSortCols.get(i) )){
//					sOrder += aColumns[iSortCols.get(i)]+ " " + sSortDirs.get(0) + ", ";
//				}
//				
//			}
//		}
		
		String sOrder= "";
		if(iSortCols.get(0)!=null){
			sOrder = " ORDER BY  ";
			for(int i=0; i<iSortingCols; i++){
				if(bSortables.get(iSortCols.get(i)) == true){
					sOrder += aColumns[iSortCols.get(i)] + " " + sSortDirs.get(i) + ", ";
				}
			}
			
			sOrder=sOrder.substring(0, sOrder.length()-2);
			if(sOrder.equals(" ORDER BY")){
				sOrder="";
			}
		}
		
		
		/* Searching */
		String sWhere = null;
		if(addicionalWhere==null)
			sWhere= " where  1=1 ";
		else
			sWhere = addicionalWhere+" ";
		
		if (sSearch != "")
		{
			sWhere+=" and ( ";
			for ( int i=0 ; i< aColumns.length ; i++ )
			{
				sWhere += "lower(cast("+aColumns[i]+" as varchar))"+" LIKE '%"+sSearch.toLowerCase()+"%' OR ";
			}
			sWhere = sWhere.substring(0, sWhere.length()-3 );
			sWhere += ") ";
		}
		
		/* Individual column filtering  */
		for (int i =0; i<aColumns.length; i++) {
			if(i<bSearchables.size()){
				if ( bSearchables.get(i) == true && sSearchs.get(i) != null )
				{
					sWhere += " AND ";
					sWhere += aColumns[i]+ " LIKE '%"+sSearchs.get(i)+"%' ";
				}
			}
		}
		
		String columns="*";
//		for (int i = 0; i < aColumns.length; i++) {
//			columns += aColumns[i]+", ";
//		}
//		columns = columns.substring(0, columns.length()-2 );
		table = " from "+table;
		
		
		StringBuilder sql = new StringBuilder();
		sql.append("select ")
		.append(columns)
		.append(table)
		.append(sWhere)
		.append(sOrder)
		.append(sLimit);
		
		return sql.toString();
		
	}

	@SuppressWarnings("unused")
	public static String doBServerSideITotalDisplayRecordsSql(Map<String,String[]> parameters, String table, String addicionalWhere){
		
		HashMap<Integer,Boolean> bRegexs = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSearchables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSortables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Integer> iSortCols = new HashMap<Integer,Integer>();
		HashMap<Integer,String> mDataProps = new HashMap<Integer,String>();
		HashMap<Integer,String> sSearchs = new HashMap<Integer,String>();
		HashMap<Integer,String> sSortDirs = new HashMap<Integer,String>();
		HashMap<Integer,String> columnNames = new HashMap<Integer,String>();
		
		for (Entry<String,String[]> entry : parameters.entrySet()) {
			if(entry.getKey().startsWith("bRegex_")){
				bRegexs.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSearchable_")){
				bSearchables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSortable_")){
				bSortables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("iSortCol_")){
				iSortCols.put(Integer.parseInt(entry.getKey().split("_")[1]), Integer.parseInt(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("mDataProp_")){
				mDataProps.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSearch_")){
				sSearchs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSortDir_")){
				sSortDirs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("columName_")){
				columnNames.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}
		}
		
		String[] aColumns = new String[columnNames.values().size()];
		int contador=0; 
		for (String columnName : columnNames.values()) {
			aColumns[contador]=columnName;
			contador++;
		}
		
		 
		
		if(iSortCols.size()<bSearchables.size()){
			for (Entry<Integer,Boolean> entry : bSearchables.entrySet()) {
				if(!iSortCols.containsKey(entry.getKey())){
					iSortCols.put(entry.getKey(), null);
				}
			}
		}
		
		for (Entry<Integer,String> entry : sSearchs.entrySet()) {
			if(entry.getValue().isEmpty()){
				entry.setValue(null);
			}
		}
			
		
		Boolean bRegex = Boolean.parseBoolean(parameters.get("bRegex")[0]);
		Integer iColumns = Integer.parseInt(parameters.get("iColumns")[0]);
		Integer iDisplayLength = Integer.parseInt(parameters.get("iDisplayLength")[0]);
		Integer iDisplayStart = Integer.parseInt(parameters.get("iDisplayStart")[0]);
		Integer iSortingCols = Integer.parseInt(parameters.get("iSortingCols")[0]);
		String sColumns = parameters.get("sColumns")[0];
		Integer sEcho = Integer.parseInt(parameters.get("sEcho")[0]);		
		String sSearch = parameters.get("sSearch")[0];
		sSearch = sSearch.replaceAll("'", "").replaceAll("\"", "").replaceAll("\'", "");
		
		
		
		
		/* Searching */
		String sWhere = null;
		if(addicionalWhere==null)
			sWhere= " where  1=1 ";
		else
			sWhere = addicionalWhere+" ";
		
		if (sSearch != "")
		{
			sWhere+=" and ( ";
			for ( int i=0 ; i< aColumns.length ; i++ )
			{
				sWhere += "lower(cast("+aColumns[i]+" as varchar))"+" LIKE '%"+sSearch.toLowerCase()+"%' OR ";
			}
			sWhere = sWhere.substring(0, sWhere.length()-3 );
			sWhere += ") ";
		}
		
		/* Individual column filtering  */
		for (int i =0; i<aColumns.length; i++) {
			if(i<bSearchables.size()){
				if ( bSearchables.get(i) == true && sSearchs.get(i) != null )
				{
					sWhere += " AND ";
					sWhere += aColumns[i]+ " LIKE '%"+sSearchs.get(i)+"%' ";
				}
			}
		}
		
		String columns=" count(*) ";
//		for (int i = 0; i < aColumns.length; i++) {
//			columns += aColumns[i]+", ";
//		}
//		columns = columns.substring(0, columns.length()-2 );
		table = " from "+table;
		
		
		StringBuilder sql = new StringBuilder();
		sql.append("select ")
		.append(columns)
		.append(table)
		.append(sWhere);
		
		String sqlMontado = sql.toString();
		return sqlMontado;
		
	}

	@SuppressWarnings("unused")
	public static String doBServerSideITotalRecordsSql(Map<String,String[]> parameters, String table, String addicionalWhere){
		
		HashMap<Integer,Boolean> bRegexs = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSearchables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Boolean> bSortables = new HashMap<Integer,Boolean>();
		HashMap<Integer,Integer> iSortCols = new HashMap<Integer,Integer>();
		HashMap<Integer,String> mDataProps = new HashMap<Integer,String>();
		HashMap<Integer,String> sSearchs = new HashMap<Integer,String>();
		HashMap<Integer,String> sSortDirs = new HashMap<Integer,String>();
		HashMap<Integer,String> columnNames = new HashMap<Integer,String>();
		
		for (Entry<String,String[]> entry : parameters.entrySet()) {
			if(entry.getKey().startsWith("bRegex_")){
				bRegexs.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSearchable_")){
				bSearchables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("bSortable_")){
				bSortables.put(Integer.parseInt(entry.getKey().split("_")[1]), Boolean.parseBoolean(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("iSortCol_")){
				iSortCols.put(Integer.parseInt(entry.getKey().split("_")[1]), Integer.parseInt(entry.getValue()[0]));
			}else if(entry.getKey().startsWith("mDataProp_")){
				mDataProps.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSearch_")){
				sSearchs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("sSortDir_")){
				sSortDirs.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}else if(entry.getKey().startsWith("columName_")){
				columnNames.put(Integer.parseInt(entry.getKey().split("_")[1]), entry.getValue()[0]);
			}
		}
		
		String[] aColumns = new String[columnNames.values().size()];
		int contador=0; 
		for (String columnName : columnNames.values()) {
			aColumns[contador]=columnName;
			contador++;
		}
		
		
		
		if(iSortCols.size()<bSearchables.size()){
			for (Entry<Integer,Boolean> entry : bSearchables.entrySet()) {
				if(!iSortCols.containsKey(entry.getKey())){
					iSortCols.put(entry.getKey(), null);
				}
			}
		}
		
		for (Entry<Integer,String> entry : sSearchs.entrySet()) {
			if(entry.getValue().isEmpty()){
				entry.setValue(null);
			}
		}
		
		
		Boolean bRegex = Boolean.parseBoolean(parameters.get("bRegex")[0]);
		Integer iColumns = Integer.parseInt(parameters.get("iColumns")[0]);
		Integer iDisplayLength = Integer.parseInt(parameters.get("iDisplayLength")[0]);
		Integer iDisplayStart = Integer.parseInt(parameters.get("iDisplayStart")[0]);
		Integer iSortingCols = Integer.parseInt(parameters.get("iSortingCols")[0]);
		String sColumns = parameters.get("sColumns")[0];
		Integer sEcho = Integer.parseInt(parameters.get("sEcho")[0]);		
		String sSearch = parameters.get("sSearch")[0];
		sSearch = sSearch.replaceAll("'", "").replaceAll("\"", "").replaceAll("\'", "");
		
		
		
		
		String columns=" count(*) ";
//		for (int i = 0; i < aColumns.length; i++) {
//			columns += aColumns[i]+", ";
//		}
//		columns = columns.substring(0, columns.length()-2 );
		table = " from "+table;
		
		
		StringBuilder sql = new StringBuilder();
		sql.append("select ")
		.append(columns)
		.append(table);
		
		String sqlMontado = sql.toString();
		return sqlMontado;
		
	}

}
